package com.attendance.www.hr.service.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Service;

import com.attendance.www.hr.beans.AttachmentBean;
import com.attendance.www.hr.beans.UserBean;
import com.attendance.www.hr.constant.AppConstant;
import com.attendance.www.hr.dao.mapper.EmployeeExtMapper;
import com.attendance.www.hr.dao.mapper.EmployeeMapper;
import com.attendance.www.hr.dao.mapper.EmployeeSchedulMapper;
import com.attendance.www.hr.dao.mapper.PunchTimeMapper;
import com.attendance.www.hr.dao.mapper.SchedulWeekMapper;
import com.attendance.www.hr.dao.mapper.SystemConfigureMapper;
import com.attendance.www.hr.dao.mapper.UserExtMapper;
import com.attendance.www.hr.dao.mapper.UserMapper;
import com.attendance.www.hr.entitys.Employee;
import com.attendance.www.hr.entitys.EmployeeExample;
import com.attendance.www.hr.entitys.EmployeeSchedul;
import com.attendance.www.hr.entitys.PunchTime;
import com.attendance.www.hr.entitys.PunchTimeExample;
import com.attendance.www.hr.entitys.SchedulWeek;
import com.attendance.www.hr.entitys.SchedulWeekExample;
import com.attendance.www.hr.entitys.SystemConfigure;
import com.attendance.www.hr.entitys.SystemConfigureExample;
import com.attendance.www.hr.entitys.User;
import com.attendance.www.hr.entitys.UserExample;
import com.attendance.www.hr.enums.EmployeeCategoryEnum;
import com.attendance.www.hr.enums.EmployeeTypeEnum;
import com.attendance.www.hr.enums.IsVisaWaiverEnum;
import com.attendance.www.hr.enums.PrivilegeGroupEnum;
import com.attendance.www.hr.enums.RegionTypeEnum;
import com.attendance.www.hr.enums.SexEnum;
import com.attendance.www.hr.exception.ResultFaultException;
import com.attendance.www.hr.service.IEmployeeExcelService;
import com.zhangkongli.frame.utils.MD5Util;

@Service
public class EmployeeExcelServiceImpl implements IEmployeeExcelService {
	/**
	 * 邮箱正则表达式
	 */
	private final String RULE_EMAIL = "^\\w+((-\\w+)|(\\.\\w+))*\\@[A-Za-z0-9]+((\\.|-)[A-Za-z0-9]+)*\\.[A-Za-z0-9]+$";
	private final Pattern EMAIL_PATTERN = Pattern.compile(RULE_EMAIL);
	@Resource
	private EmployeeMapper employeeDao;
	@Resource
	private EmployeeExtMapper employeeExtDao;
	@Resource
	private PunchTimeMapper punchTimeDao;
	@Resource
	private SchedulWeekMapper schedulWeekDao;
	@Resource
	private EmployeeSchedulMapper employeeSchedulDao;
	@Resource
	private SystemConfigureMapper systemConfigureDao;
	@Resource
	private UserExtMapper userExtDao;
	@Resource
	private UserMapper userDao;
	/**
	 * 数据库实体map
	 */
	private Map<Integer, UserBean> userMap;
	/**
	 * 导入表原员工no Map
	 */
	private Map<String, Integer> usernameMap;
	/**
	 * 数据库实体map
	 */
	private Map<String, Employee> employeeEntitysMap;
	/**
	 * 数据库实体map
	 */
	private Map<String, UserBean> oldEmployeeEntitysMap;
	/**
	 * 导入表员工no Map
	 */
	private Map<String, Integer> employeeMap;
	/**
	 * 员工邮箱 map
	 */
	private Map<String, Integer> emailMap;

	/**
	 * 删除公司相关数据
	 * 
	 * @return
	 */
	@Override
	public boolean delCorporation() {
		return true;
	}

	@Override
	public List<String> importEmployee(Workbook work, AttachmentBean attachmentBean) {
		SystemConfigure systemConfigure = systemConfigureDao.selectByExample(new SystemConfigureExample()).get(0);
		Date effectiveDate = systemConfigure.getEffectiveDate();
		if (effectiveDate != null) {
			if (attachmentBean.getFromDate().getTime() - effectiveDate.getTime() >= 0) {
				SystemConfigure systemConfigure1 = new SystemConfigure();
				systemConfigure1.setEffectiveDate(attachmentBean.getFromDate());
				systemConfigureDao.updateByExampleSelective(systemConfigure1, new SystemConfigureExample());
			}
		}

		Calendar calendar = Calendar.getInstance(); // 得到日历
		calendar.setTime(attachmentBean.getFromDate());// 把当前时间赋给日历
		calendar.add(Calendar.DAY_OF_MONTH, -1); // 设置为前一天
		Date ThruDate = calendar.getTime(); // 得到前一天的时间
		Date oldThruDate = null;
		Date oldUserThruDate = null;

		oldEmployeeEntitysMap = new HashMap<String, UserBean>();
		employeeEntitysMap = new HashMap<String, Employee>();
		employeeMap = new HashMap<String, Integer>();
		emailMap = new HashMap<String, Integer>();
		userMap = new HashMap<Integer, UserBean>();
		usernameMap = new HashMap<String, Integer>();

		if (attachmentBean.getType() == 3) {
			EmployeeExample example = new EmployeeExample();
			EmployeeExample.Criteria criteria = example.createCriteria();
			criteria.andThruDateIsNull();
			Employee employee = new Employee();
			employee.setThruDate(ThruDate);
			employeeDao.updateByExampleSelective(employee, example);
			UserExample userExample = new UserExample();
			UserExample.Criteria userCriteria = userExample.createCriteria();
			userCriteria.andThruDateIsNull();
			User user = new User();
			user.setThruDate(ThruDate);
			userDao.updateByExampleSelective(user, userExample);
			List<UserBean> userList = userExtDao.getUserList(attachmentBean.getFromDate(), ThruDate);
			for (UserBean userBean : userList) {
				oldEmployeeEntitysMap.put(userBean.getEmployeeNo(), userBean);
			}
		}
		PunchTimeExample punchTimeExample = new PunchTimeExample();
		punchTimeExample.createCriteria().andIsDefaultEqualTo(1);
		List<PunchTime> punchTimeList = punchTimeDao.selectByExample(punchTimeExample);
		int punchTimeId = 0;
		int swId = 0;
		SchedulWeekExample schedulWeekExample = new SchedulWeekExample();
		schedulWeekExample.createCriteria().andIsDefaultEqualTo(1);
		List<SchedulWeek> schedulWeekList = schedulWeekDao.selectByExample(schedulWeekExample);
		Date thruDate = null;
		List<String> successList = new ArrayList<String>();
		if (attachmentBean.getType() != 3) {
			// 判断导入类型 1.覆盖 2.增量
			List<Employee> employeeList = employeeExtDao.getEmployeeList(attachmentBean.getFromDate(),
					attachmentBean.getFromDate());

			for (Employee employee : employeeList) {
				employeeEntitysMap.put(employee.getEmployeeNo(), employee);
				employeeMap.put(employee.getEmployeeNo(), -1);
				emailMap.put(employee.getEmail(), -1);
				oldThruDate = employee.getThruDate();
			}
			List<UserBean> userBeanList = userExtDao.getUserList(attachmentBean.getFromDate(),
					attachmentBean.getFromDate());
			for (UserBean user : userBeanList) {

				userMap.put(user.getEmployeeId(), user);
				usernameMap.put(user.getUserName(), -1);
				oldUserThruDate = user.getThruDate();
			}
		}

		// 获取工作表的数量
		int sheetNum = work.getNumberOfSheets();
		// 遍历工作表 找出要导入的Excel表
		for (int m = 0; m < sheetNum; m++) {
			Sheet sh = work.getSheetAt(m);
			if (sh.getSheetName().equals("1.1员工")) {
				Sheet sheet = work.getSheetAt(m);
				List<String> errorMsgList = new ArrayList<String>();// 创建错误信息列表
				// 扫描Excel每行的每个单元格
				for (int i = 1; i <= sheet.getLastRowNum(); i++) {
					Employee employee = new Employee();// 创建公司对象
					User user = new User();// 创建公司对象
					StringBuilder str = new StringBuilder("第" + (i + 1) + "行");
					Row row = sheet.getRow(i);
					// 如果一整行为空则忽略此行继续遍历
					if (row == null) {
						continue;
					}
					// 遍历某一行的所有列，如果此行每列都为空则跳过继续下一行
					boolean isNull = true;
					for (int c = 0; c < row.getLastCellNum(); c++) {
						if (row.getCell(c) == null || StringUtils.isBlank(row.getCell(c).toString())) {
							continue;
						} else {
							isNull = false;
						}
					}
					if (isNull) {
						continue;
					}
					if (oldThruDate != null) {
						if (oldThruDate.getTime() >= attachmentBean.getFromDate().getTime()) {
							employee.setThruDate(oldThruDate);
						}
					}
					employee.setFromDate(attachmentBean.getFromDate());
					if (oldUserThruDate != null) {
						if (oldUserThruDate.getTime() >= attachmentBean.getFromDate().getTime()) {
							user.setThruDate(oldUserThruDate);
						}
					}
					user.setFromDate(attachmentBean.getFromDate());

					// 判断第一列原员工编码的合法性
					if (row.getCell(0) != null && StringUtils.isNotBlank(row.getCell(0).toString())) {
						String employeeNo = row.getCell(0).toString();
						// 判断是否为数字
						if (row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							employeeNo = new BigDecimal(row.getCell(0).getNumericCellValue()).intValue()+"";
						}else{
							employeeNo = row.getCell(0).toString();
						}
						employee.setEmployeeNo(employeeNo);
						if (attachmentBean.getType() == 1) {
							if (employeeMap.containsKey(employeeNo)) {
								if (employeeMap.get(employeeNo) > 0) {
									str.append("与第" + employeeMap.get(employeeNo) + "行员工编码(" + employeeNo + ")重复,");
								} else {
									employee.setEmployeeId(employeeEntitysMap.get(employeeNo).getEmployeeId());
									employee.setEmployeeNo(employeeNo);
								}
							} else {
								str.append("员工编码(" + employeeNo + ")不存在,");
							}
						} else {
							if (employeeMap.containsKey(employeeNo)) {
								if (employeeMap.get(employeeNo) > 0) {
									str.append("与第" + employeeMap.get(employeeNo) + "行员工编码(" + employeeNo + ")重复,");
								} else {
									str.append("员工编码(" + employeeNo + ")已存在,");
								}
							} else {
								employee.setEmployeeNo(employeeNo);
							}
						}
					} else {
						str.append("员工编码不能为空,");
					}

					// 判断第二列中文名的合法性
					if (row.getCell(1) != null && StringUtils.isNotBlank(row.getCell(1).toString())) {
						employee.setChineseName(row.getCell(1).toString());
					} else {
						str.append("中文名不能为空,");
					}

					// 第三列英文名
					if (row.getCell(2) != null && StringUtils.isNotBlank(row.getCell(2).toString())) {
						employee.setEnglishName(row.getCell(2).toString());
					}
					// 第四列性别
					// 判断第四列性别
					if (row.getCell(3) != null && StringUtils.isNotBlank(row.getCell(3).toString())) {
						Integer sex = SexEnum.getName(row.getCell(3).toString());
						if (sex != null) {
							employee.setSex(sex);
						} else {
							str.append("性别格式不正确,");
						}
					} else {
						str.append("性别不能为空,");
					}
					// 第五列邮箱
					// 判断第五列邮箱
					if (row.getCell(4) != null && StringUtils.isNotBlank(row.getCell(4).toString())) {
						String email = row.getCell(4).toString();
						String oldEmail = "";
						employee.setEmail(email);
						try {
							if (attachmentBean.getType() == 1)
								oldEmail = employeeEntitysMap.get(employee.getEmployeeNo()).getEmail();
						} catch (Exception e) {

						}
						Matcher isMatch = EMAIL_PATTERN.matcher(email);
						if (isMatch.matches()) {
							if (oldEmail != null ) {

								if (oldEmail.equals(email)) {
									employee.setEmail(email);
								}else {
									if (emailMap.containsKey(email)) {
										if (emailMap.get(email) > 0) {
											str.append("与第" + emailMap.get(email) + "行邮箱(" + email + ")重复,");
										} else {
											str.append("邮箱已存在,");
										}
									} else {
										employee.setEmail(email);
									}
								}
							} else {
								if (emailMap.containsKey(email)) {
									if (emailMap.get(email) > 0) {
										str.append("与第" + emailMap.get(email) + "行邮箱(" + email + ")重复,");
									} else {
										str.append("邮箱已存在,");
									}
								} else {
									employee.setEmail(email);
								}
							}
							emailMap.put(employee.getEmail(), i + 1);
						} else {
							str.append("邮箱格式不正确,");
						}
					}
					// 第六列地区
					// 判断第六列地区
					if (row.getCell(5) != null && StringUtils.isNotBlank(row.getCell(5).toString())) {
						Integer regionType = RegionTypeEnum.getName(row.getCell(5).toString());
						if (regionType != null) {
							employee.setRegionType(regionType);
						} else {
							str.append("地区格式不正确,");
						}
					} else {
						str.append("地区不能为空,");
					}
					// 判断第七列入职时间
					if (row.getCell(6) != null && StringUtils.isNotBlank(row.getCell(6).toString())) {
						Date fromDate = new Date();
						try {
							fromDate = row.getCell(6).getDateCellValue();
						} catch (Exception e) {
							str.append("入职时间格式不正确,");
						}
						if (fromDate != null) {
							employee.setEntryDate(fromDate);
						} else {
							str.append("入职时间格式不正确,");
						}
					} else {
						str.append("入职时间不能为空,");
					}
					// 判断第八列首次工作时间
					if (row.getCell(7) != null && StringUtils.isNotBlank(row.getCell(7).toString())) {
						Date fromDate = new Date();
						try {
							fromDate = row.getCell(7).getDateCellValue();
						} catch (Exception e) {
							str.append("首次工作时间格式不正确,");
						}
						if (fromDate != null) {
							employee.setFirstDate(fromDate);
						} else {
							str.append("首次工作时间格式不正确,");
						}
					} else {
						str.append("首次工作时间不能为空,");
					}
					if (employee.getFirstDate() != null && employee.getEntryDate() != null) {
						if (employee.getFirstDate().getTime() > employee.getEntryDate().getTime()) {
							str.append("入职时间小于首次工作时间,");
						}
					}

					if (row.getCell(8) != null && StringUtils.isNotBlank(row.getCell(8).toString())) {
						String userName = row.getCell(8).toString();
						if (attachmentBean.getType() == 1) {
							if (usernameMap.containsKey(userName)) {
								if (userMap.get(employee.getEmployeeId()).getUserName().equals(userName)) {
									user.setUsername(userName);
								} else {
									if (usernameMap.get(userName) > 0) {
										str.append("与第" + usernameMap.get(userName) + "行用户名(" + userName + ")重复,");
									} else {
										str.append("用户名(" + userName + ")已存在,");
									}
								}
							} else {
								user.setUsername(userName);
							}
						} else {
							if (usernameMap.containsKey(userName)) {
								if (usernameMap.get(userName) > 0) {
									str.append("与第" + usernameMap.get(userName) + "行用户名(" + userName + ")重复,");
								} else {
									str.append("用户名(" + userName + ")已存在,");
								}

							} else {
								user.setUsername(userName);
							}
						}

					} else {
						str.append("用户名不能为空,");
					}

					// 判断第二列中文名的合法性
					if (row.getCell(9) != null && StringUtils.isNotBlank(row.getCell(9).toString())) {
						Integer privilegeGroupId = PrivilegeGroupEnum.getName(row.getCell(9).toString());
						if (privilegeGroupId != null) {
							user.setPrivilegeGroupId(privilegeGroupId);
						} else {
							str.append("所属权限组格式不正确,");
						}
					} else {
						str.append("所属权限组不能为空,");
					}
					// 第九列员工类型
					// 判断第九列员工类型
					if (row.getCell(10) != null && StringUtils.isNotBlank(row.getCell(10).toString())) {
						Integer employeeType = EmployeeTypeEnum.getName(row.getCell(10).toString());
						if (employeeType != null) {
							employee.setEmployeeType(employeeType);
						} else {
							str.append("员工类型不正确,");
						}
					}
					// 第十列员工类别
					// 判断第十列员工类别
					if (row.getCell(11) != null && StringUtils.isNotBlank(row.getCell(11).toString())) {
						Integer employeeCategory = EmployeeCategoryEnum.getName(row.getCell(11).toString());
						if (employeeCategory != null) {
							employee.setEmployeeCategory(employeeCategory);
						} else {
							str.append("员工类别不正确,");
						}
					}

					// 判断第十一列年假可使用日期
					if (row.getCell(12) != null && StringUtils.isNotBlank(row.getCell(12).toString())) {
						Date fromDate = new Date();
						try {
							fromDate = row.getCell(12).getDateCellValue();
						} catch (Exception e) {
							str.append("年假可使用日期格式不正确,");
						}
						if (fromDate != null) {
							employee.setAnnualLeaveDate(fromDate);
						} else {
							str.append("年假可使用日期格式不正确,");
						}
					}
					if (employee.getAnnualLeaveDate() != null && employee.getEntryDate() != null) {
						if (employee.getAnnualLeaveDate().getTime() < employee.getEntryDate().getTime()) {
							str.append("入职时间大于年假可使用时间,");
						}
					}

					// 第十二列免签类型
					// 判断第十二列免签类型
					if (row.getCell(13) != null && StringUtils.isNotBlank(row.getCell(13).toString())) {
						Integer isVisaWaiver = IsVisaWaiverEnum.getName(row.getCell(13).toString());
						if (isVisaWaiver != null) {
							employee.setIsVisaWaiver(isVisaWaiver);
						} else {
							str.append("免签类型不正确,");
						}
					}
					// 第十三列员工级别
					// 判断第十三列员工级别
					if (row.getCell(14) != null && StringUtils.isNotBlank(row.getCell(14).toString())) {
						Integer level = 0;
						try {
							BigDecimal big = new BigDecimal(row.getCell(14).getNumericCellValue());
							level = big.intValue();
						} catch (Exception e) {
							str.append("员工级别格式不正确,");
						}

						employee.setLevel(level);
					}
					employee.setEmployeeIsSeparated(0);
					MD5Util md5 = new MD5Util();
					String mdPassWord = md5.getMD5ofStr(AppConstant.PASSWORD);
					user.setPassword(mdPassWord);
					if (attachmentBean.getType() == 3) {
						if (oldEmployeeEntitysMap.containsKey(employee.getEmployeeNo())) {
							user.setPassword(oldEmployeeEntitysMap.get(employee.getEmployeeNo()).getPassword());
						}
					}
					if (str.length() == new String("第" + (i + 1) + "行").length()) {
						// 获取EmployeeId下的Employee_schedul信息
						
						try {
							punchTimeId = punchTimeList.get(0).getPunchTimeId();
						} catch (Exception e) {
							throw new ResultFaultException("11_00001");
						}

						// 获取默认状态下的班次表Id

						try {
							swId = schedulWeekList.get(0).getSwId();
							thruDate = schedulWeekList.get(0).getThruDate();
						} catch (Exception e) {
							throw new ResultFaultException("11_00002");
						}

						// 判断导入类型 1.覆盖 2.增量
						if (attachmentBean.getType() == 1) {
							boolean t = true;
							if (employee.getEmployeeId() != null) {
								employee.setUpdateTime(new Date());
								employee.setCreateTime(
										employeeEntitysMap.get(employee.getEmployeeNo()).getCreateTime());
								employeeDao.updateByPrimaryKey(employee);
								user.setUpdateTime(new Date());
								user.setCreateTime(userMap.get(employee.getEmployeeId()).getCreateTime());
								user.setPassword(userMap.get(employee.getEmployeeId()).getPassword());
								userDao.updateByPrimaryKey(user);
								String success = str.toString() + "员工编号(" + employee.getEmployeeNo() + ")";
								successList.add(success);
								t = false;
							}
							if (t) {
								try {
									// 添加
									employeeDao.insertSelective(employee);
									user.setEmployeeId(employee.getEmployeeId());
									userDao.insertSelective(user);

									int employeeId = employee.getEmployeeId();
									EmployeeSchedul employeeSchedul = new EmployeeSchedul();
									employeeSchedul.setEmployeeId(employeeId);
									employeeSchedul.setSwId(swId);
									employeeSchedul.setPunchTimeId(punchTimeId);
									employeeSchedul.setFromDate(employee.getEntryDate());
									employeeSchedul.setIsDelete(0);
									employeeSchedul.setThruDate(thruDate);
									employeeSchedulDao.insertSelective(employeeSchedul);
								} catch (Exception e) {
									throw new ResultFaultException("9_00001");
								}
							}
						} else {
							boolean t = true;
							if (t) {
								try {
									// 添加
									employee.setFromDate(employee.getEntryDate());//新增员工开始日期，是入职日期
									employeeDao.insertSelective(employee);
									user.setFromDate(employee.getEntryDate());//新增员工user开始日期，是入职日期
									user.setEmployeeId(employee.getEmployeeId());
									userDao.insertSelective(user);

									int employeeId = employee.getEmployeeId();
									EmployeeSchedul employeeSchedul = new EmployeeSchedul();
									employeeSchedul.setEmployeeId(employeeId);
									employeeSchedul.setSwId(swId);
									employeeSchedul.setPunchTimeId(punchTimeId);
									employeeSchedul.setFromDate(employee.getEntryDate());
									employeeSchedul.setIsDelete(0);
									employeeSchedul.setThruDate(thruDate);
									employeeSchedulDao.insertSelective(employeeSchedul);
								} catch (Exception e) {
									throw new ResultFaultException("9_00001");
								}
							}
						}
						UserBean userBean = new UserBean();
						userBean.setEmployeeNo(row.getCell(8).toString());
						userBean.setEmployeeId(user.getEmployeeId());
						userBean.setUserName(user.getUsername());
						employeeMap.put(employee.getEmployeeNo(), i+1);
						userMap.put(employee.getEmployeeId(), userBean);
						employeeEntitysMap.put(employee.getEmployeeNo(), employee);
					} else {
						String errorStr = str.toString().substring(0, str.toString().length() - 1);
						errorMsgList.add(errorStr);
					}
				}
				if (errorMsgList.size() > 0) {
					throw new ResultFaultException("9_00001", errorMsgList);
				}

				if (successList.size() > 0) {
					successList.add(0, "员工导入完成,并覆盖数据如下：");
				} else {
					successList.add(0, "员工导入完成");
				}
				return successList;
			}
		}
		throw new ResultFaultException("9_00011");
	}
}
